<?php

/**
 * Subclass for performing query and update operations on the 'trathuong' table.
 *
 * 
 *
 * @package lib.model
 */ 
class TrathuongPeer extends BaseTrathuongPeer
{
	public static function getPaidBonus($phone){
        $con = new MysqlDAL('propel');
        $con->safe($phone, 0);
        $sql = "SELECT phone as phone,
            sum(bonus) as sum_bonus
            FROM trathuong
            WHERE phone = '".$phone."' ";
        $sql.= " GROUP BY phone";
        return $con->doSelect($sql);
    }
    public static function getListPaided($phone,$year){
        $con = new MysqlDAL('propel');
        $con->safe($phone, 0);
        $sql = "SELECT            
            bonus as bonus,
            DATE(paid_date) as paidDate
            FROM trathuong
            WHERE phone = '$phone'";
        if ($year)
            $sql .="AND (YEAR(paid_date) = $year) ";
       
        return $con->doSelect($sql);
    }
    public static function getTimePaid($phone){
        $con = new MysqlDAL('propel');
        $con->safe($phone, 0);
        $sql = "SELECT            
            Year(paid_date) as year
            FROM trathuong
            WHERE phone = '$phone'
            Group by year
            Order by year desc";
        return $con->doSelect($sql);
    }
	public static function getLastPaid(){
        $con = new MysqlDAL('propel');
        $sql = "select max(paid_date) as max_date from trathuong";
        $ret = $con->doSelect($sql);
        return $ret[0]['max_date'];
    }
    public static function getPaid(){
        $con = new MysqlDAL('propel');
        $sql = "select min(paid_date) as min_date from trathuong";
        $ret = $con->doSelect($sql);
        return $ret[0]['min_date'];
    }
 	public static function getCurrentBonus($phone){
        $con = new MysqlDAL('propel');
        $con->safe($phone, 0);

        $max_date = TrathuongPeer::getLastPaid();

        $sql = "select
            user.phone as phone,
            user.username,
            sum(ringtone_download.amount) as sum_bonus
            from user inner join ringtone on (user.id = ringtone.creator_id)
            inner join ringtone_download on (ringtone.id = ringtone_download.ringtone_id)
            where date(ringtone_download.download_datetime) > date('".$max_date."') ";
        $sql.= " AND phone = '".$phone."' ";
        $sql.= " GROUP BY phone";
        return $con->doSelect($sql);
    }

    public static function getCurrentBonusOfBilling($phone){
        $con = new MysqlDAL('propel');
        $con->safe($phone, 0);
        $sql = "SELECT phone as phone,
            bonus as bonus
            FROM trathuong
            WHERE phone = '".$phone."' ";
        $sql.= " AND (month(paid_date) = month(now()) - 1) ";
        $sql.= " AND (YEAR(paid_date) = YEAR(now())) ";
        return $con->doSelect($sql);
    }
}
